--EXEC sp_create_update_all_inventory_receive_lot 19, 'G1V999.1','G1V999.1', 1, 1, 'CARSEM','PRE_GRIND', 'Admin',9442,25960,'21','', 0
ALTER PROCEDURE sp_create_update_all_inventory_receive_lot
(
	@SHIP_LOC_PROCESSSTEP_ID	INT,
	@LOT_NUMBER		 VARCHAR(50),
	@FAB_LOT_NUMBER  VARCHAR(50),
	@QTY_SHIPPED     INT,
	@QTY_RECEIVED     INT,
	@TO_VENDOR_NAME     VARCHAR(50),
	@TO_PS_TYPE  VARCHAR(50),
	@USER_NAME		 VARCHAR(50),
	@PREV_SHIPLOT_ID INT,
	@PREV_LOT_ID	 INT,
	@CHECKED_WAFERIDS VARCHAR(100),
	@UNCHECKED_WAFERIDS VARCHAR(100),
    @REMAIN_WAFER_QTY	INT
)
AS 
BEGIN
	DECLARE @LOCATION_ID		INT
	DECLARE @PROCESS_STEP_ID	INT
	DECLARE @DATE_IN			DATETIME
	DECLARE @LOT_ID				INT
	DECLARE @PREGRIND_LOT_ID	INT
    DECLARE @PS_TYPE_NAME		VARCHAR(50)
	DECLARE @LOT_STATUS			VARCHAR(20)
	DECLARE @WAFER_ID_TABLE	 TABLE ( WAFER_ID INT)
	DECLARE @CHECKED_WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @WAFER_ID			INT
	DECLARE @WAFER_ID_STR	    VARCHAR(2)
	DECLARE @PREGRIND_LOT_NO	VARCHAR(50)
	DECLARE @REMAIN_DIE_QTY		INT
	DECLARE @SHIP_FROM_PROCESS  VARCHAR(50)

	Select @SHIP_FROM_PROCESS = PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @SHIP_LOC_PROCESSSTEP_ID

	SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
	PRINT @SHIP_FROM_PROCESS
	SELECT @PS_TYPE_NAME = Base_Process_Type FROM PROCESS_TYPES_TABLE WHERE Process_TYPE = @TO_PS_TYPE
	IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' 
	BEGIN
			IF @REMAIN_WAFER_QTY = 0
			BEGIN
				SET @REMAIN_WAFER_QTY = @QTY_SHIPPED - @QTY_RECEIVED
			END
	END
	
	IF @SHIP_FROM_PROCESS = 'Die Bank'
	BEGIN
			SET @REMAIN_DIE_QTY = @QTY_SHIPPED - @QTY_RECEIVED
	END	

	SELECT TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SHIP_LOC_PROCESSSTEP_ID,1 )  WHERE PS_TYPE = @TO_PS_TYPE
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @TO_VENDOR_NAME
	
	IF @PROCESS_STEP_ID IS NOT NULL 
	BEGIN
		IF @TO_PS_TYPE = 'PRE_GRIND'
		BEGIN
				SET @CHECKED_WAFER_IDS_AS_COMMA = REPLACE( @CHECKED_WAFERIDS, ';', ',' )

				INSERT INTO @WAFER_ID_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @CHECKED_WAFER_IDS_AS_COMMA )

				DECLARE @wafer_cursor CURSOR 
				SET @wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM @WAFER_ID_TABLE
				OPEN @wafer_cursor
				FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
				WHILE @@FETCh_STATUS = 0
				BEGIN				
						PRINT @WAFER_ID		
						IF @WAFER_ID < 10
						BEGIN
							
							SET @WAFER_ID_STR = '0' + CONVERT(VARCHAR,@WAFER_ID)
						END
						ELSE
						BEGIN
							SET @WAFER_ID_STR = @WAFER_ID
						END
						
						SET @PREGRIND_LOT_NO = substring(@LOT_NUMBER, 0 , charindex('.',@LOT_NUMBER ) ) + '-' + @WAFER_ID_STR
						PRINT @PREGRIND_LOT_NO
						SELECT @PREGRIND_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @PREGRIND_LOT_NO
						IF @PREGRIND_LOT_ID IS NULL
						BEGIN
							INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, USER_NAME) 
								VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @PREGRIND_LOT_NO, @FAB_LOT_NUMBER, @PREGRIND_LOT_NO, 1, @DATE_IN, null, 'Running', CURRENT_TIMESTAMP,0, 'PROD', 0, @USER_NAME)
							SELECT @PREGRIND_LOT_ID = @@IDENTITY
							INSERT INTO INVENTORY_LOT (LOT_ID, Wafer_Id, Timestamp) VALUES ( @PREGRIND_LOT_ID, @WAFER_ID, CURRENT_TIMESTAMP )  			
						END
						ELSE
						BEGIN
								SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
								IF @LOT_STATUS <> 'Close' 
								BEGIN	
									UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + 1, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
								END
								ELSE
								BEGIN
									UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', WAFER_QTY_IN = 1, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
								END
								UPDATE INVENTORY_LOT SET WAFER_ID = ISNULL(@WAFER_ID,'') , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
						END
					SET @WAFER_ID = NULL
					SET @WAFER_ID_STR = NULL
					SET @PREGRIND_LOT_NO = NULL
					SET @PREGRIND_LOT_ID = NULL
					FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
				END
				CLOSE @wafer_cursor 
				DEALLOCATE @wafer_cursor
		END
		ELSE
		BEGIN
			SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
			PRINT @LOT_ID
			IF @LOT_ID IS NULL
			BEGIN
				PRINT @PS_TYPE_NAME
				IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' 
				BEGIN
					INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, USER_NAME) 
						VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @LOT_NUMBER, @QTY_RECEIVED, @DATE_IN, null, 'Running', CURRENT_TIMESTAMP,0, 'PROD', 0, @USER_NAME)
					SELECT @LOT_ID = @@IDENTITY
				END
				ELSE
				BEGIN
					INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, USER_NAME) 
						VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @LOT_NUMBER, @QTY_RECEIVED, @DATE_IN, null, 'Running', CURRENT_TIMESTAMP,0, 'PROD', 0, @USER_NAME)
					SELECT @LOT_ID = @@IDENTITY
				END

				IF @PS_TYPE_NAME = 'FAB'
				BEGIN
					INSERT INTO FAB_LOT (LOT_ID, Wafer_Id, Timestamp) VALUES ( @LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP )  
				END
				ELSE IF @PS_TYPE_NAME = 'SORT'
				BEGIN
					INSERT INTO SORT_LOT (LOT_ID, Sl_Wafer_ID, Timestamp) VALUES ( @LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP )  
				END
				ELSE IF @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
				BEGIN
					insert into Inventory_Lot( Lot_ID, Wafer_id, Timestamp ) values (@LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP)		
				END
				ELSE IF @PS_TYPE_NAME = 'Assembly' 
				BEGIN
					insert into Assembly_Lot( Lot_ID, Timestamp ) values (@LOT_ID, CURRENT_TIMESTAMP)		
				END
				ELSE IF @PS_TYPE_NAME = 'TEST' 
				BEGIN
					insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@LOT_ID, CURRENT_TIMESTAMP)		
				END
			END
			ELSE
			BEGIN
				SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
				IF @LOT_STATUS <> 'Close' 
				BEGIN
					IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' 
					BEGIN
						UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + @QTY_RECEIVED, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
					END
					ELSE
					BEGIN
						UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @QTY_RECEIVED, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
					END
				END
				ELSE
				BEGIN
					 IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' 
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', WAFER_QTY_IN = @QTY_RECEIVED, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
					END
					ELSE
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', QTY_IN = @QTY_RECEIVED, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
					END		
				END

				IF @PS_TYPE_NAME = 'FAB'
				BEGIN
					IF @LOT_STATUS <> 'Close' 
					BEGIN
						UPDATE FAB_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
					ELSE
					BEGIN
						UPDATE FAB_LOT SET WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
				END
				ELSE IF @PS_TYPE_NAME = 'SORT'
				BEGIN
					IF @LOT_STATUS <> 'Close' 
					BEGIN
						UPDATE SORT_LOT SET SL_WAFER_ID = ISNULL(SL_WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
					ELSE
					BEGIN
						UPDATE SORT_LOT SET SL_WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
				END
				ELSE IF @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
				BEGIN
					IF @LOT_STATUS <> 'Close' 
					BEGIN
						UPDATE INVENTORY_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
					ELSE
					BEGIN
						UPDATE INVENTORY_LOT SET WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
					END
				END
			END
		END--END OF ELSE BLOCK
		
		DECLARE @SO_NO VARCHAR(50), @PO_NO	VARCHAR(50), @INVOICE VARCHAR(50), @LC INT, @PKG VARCHAR(50), @NEXT_LOC VARCHAR(50), @NEXT_PROCESS VARCHAR(50) 
		DECLARE @FLT_NO VARCHAR(50),@MAWB VARCHAR(50),@HAWB VARCHAR(50)
		DECLARE @SHIP_DATE DATETIME, @ETD DATETIME,@ETA DATETIME

		IF @REMAIN_WAFER_QTY > 0
		BEGIN
				DECLARE @SHIp_QTY1 INT
				IF @SHIP_FROM_PROCESS = 'Die Bank'
				BEGIN
						SET @SHIp_QTY1 = @REMAIN_DIE_QTY
				END
				ELSE
				BEGIN
						SET @SHIp_QTY1 = @REMAIN_WAFER_QTY
				END

				UPDATE SHIPPED_LOT SET SHIP_QTY = SHIP_QTY-@SHIp_QTY1, STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP,
					Closed_By = @USER_NAME, WAFER_ID = @CHECKED_WAFERIDS WHERE SHIPPEDLOT_ID = @PREV_SHIPLOT_ID
					
				SELECT @SHIP_DATE = SHIP_DATE, @SO_NO = SL_Customer_SO, @PO_NO = SL_Customer_PO, @INVOICE= SL_Invoice_Number, @LC=SL_Lead_Count, @PKG = SL_Package, @NEXT_LOC = SL_NextLocation, @NEXT_PROCESS = SL_NEXT_PROCESS, @MAWB = SL_MAWB_NUMBER, @HAWB = SL_HAWB_NUMBER, @FLT_NO = SL_Flight_Number,@ETD=SL_ETD, @ETA = SL_ETA FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @PREV_SHIPLOT_ID
					
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS,  SHIP_DROP_OUT, SL_CUSTOMER_SO, SL_CUSTOMER_PO, SL_INVOICE_NUMBER, SL_LEAD_COUNT, SL_PACKAGE, SL_NEXTLOCATION, SL_NEXT_PROCESS, SL_MAWB_NUMBER, SL_HAWB_NUMBER, SL_FLIGHT_NUMBER, SL_ETD, SL_ETA, WAFER_ID ) 
					VALUES( @PREV_LOT_ID, @SHIP_DATE, @SHIp_QTY1, CURRENT_TIMESTAMP, 'TRANSIT', 0, 0, @SO_NO, @PO_NO, @INVOICE, @LC, @PKG, @NEXT_LOC, @NEXT_PROCESS, @MAWB, @HAWB, @FLT_NO, @ETD, @ETA,@UNCHECKED_WAFERIDS  )
		END
		ELSE
		BEGIN
	 		 UPDATE SHIPPED_LOT SET STATUS = 'Close', SHIP_STATUS = 1 , Close_Date = CURRENT_TIMESTAMP, Closed_By = @USER_NAME WHERE SHIPPEDLOT_ID = @PREV_SHIPLOT_ID
		END
		
	END --PROCESSSTEP ID IS NOT NULL

	
	
END